const db = require('../config/db');

const getAllProducts = async () => {
  const [rows] = await db.query(`
    SELECT p.*, c.name as category_name 
    FROM products p 
    LEFT JOIN categories c ON p.category_id = c.id
  `);
  return rows;
};

const getProductsByCategory = async (categoryId) => {
  const [rows] = await db.query(`
    SELECT p.*, c.name as category_name 
    FROM products p 
    LEFT JOIN categories c ON p.category_id = c.id
    WHERE p.category_id = ?
  `, [categoryId]);
  return rows;
};

const getProductById = async (id) => {
  const [rows] = await db.query(`
    SELECT p.*, c.name as category_name 
    FROM products p 
    LEFT JOIN categories c ON p.category_id = c.id
    WHERE p.id = ?
  `, [id]);
  return rows[0];
};

const searchProducts = async (keyword) => {
  const [rows] = await db.query(`
    SELECT p.*, c.name as category_name 
    FROM products p 
    LEFT JOIN categories c ON p.category_id = c.id
    WHERE p.name LIKE ? OR p.description LIKE ?
  `, [`%${keyword}%`, `%${keyword}%`]);
  return rows;
};

module.exports = {
  getAllProducts,
  getProductsByCategory,
  getProductById,
  searchProducts
};